package com.polytech.tablespaceResolver.ORMUtils;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.security.KeyStore.Entry;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.csvreader.CsvWriter;

public class ORMUtils {
	private Connection connection = null;
	private static ORMUtils utils;
	private static String outputFile = "./result.csv";
	public static int counter = 1;
	CsvWriter csvOutput;
	
	private static final String DB_STRUCTURE_QUERY =
			"SELECT TABLE_NAME, COLUMN_NAME " +
			"FROM INFORMATION_SCHEMA.COLUMNS " +
			"WHERE TABLE_SCHEMA IN (?)";
	
	private static final String DB_LOG_QUERY = 
			"SELECT QUERY FROM POSTGRES_LOG";
	
	private static final String DB_STORING_QUERY = 
			"INSERT INTO RESOLVED_LOG (QUERY) VALUES (?)";
	
	private ORMUtils() {}
	
	public static ORMUtils getInstance()
	{
		if(utils == null)
			utils = new ORMUtils();
		return utils;
	}
	
	public void connect(DBCredentials credentials)
	{
		try {			 			
			Class.forName("org.postgresql.Driver");			
		} catch (ClassNotFoundException e) { 
			System.out.println("PostgreSQL JDBC Driver wasn't Found");
		}
 
		System.out.println("PostgreSQL JDBC Driver was Registered");		
 
		try { 
			connection = DriverManager.getConnection(	credentials.getConnectionString(), 
														credentials.getLogin(), 
														credentials.getPassword()	); 
		} catch (SQLException e) { 
			System.out.println("Connection Failed");
			e.printStackTrace();
		}
 
		if (connection != null) {
			System.out.println("DB Connection was established");
		}
	}
	
	public void disconnect()
	{
		try {
			if(connection == null)
				System.out.println("DB Connection was already closed");
			else
				connection.close();
		} catch (SQLException e) {
			System.err.println("Error occured during closing connection");
			e.printStackTrace();
		}
	}
	
	public Map <String, List<String>> getDBStructure(String schema)
	{
		List<String> columnNames; 
		Map <String, List<String>> dbStructure = new HashMap<String, List<String>>();
		
		try {
			PreparedStatement pStatement = connection.prepareStatement(DB_STRUCTURE_QUERY);
			pStatement.setString(1, schema);
			ResultSet result = pStatement.executeQuery();
			
			while (result.next()) {
				String tableName = result.getString(1);
				String columnName = result.getString(2);
				
				columnNames = dbStructure.get(tableName);
				
				if(columnNames == null)
					columnNames = new ArrayList<String>();
				
				columnNames.add(columnName);				
				dbStructure.put(tableName, columnNames);
			}
			
			pStatement.clearParameters();
			System.out.println("DB structure was successfully built");			
		} catch (SQLException e) {
			System.err.println("Error occured while feeling DB structure");
			e.printStackTrace();
		}
		
		return dbStructure;
	}
	
	public List<String> getDBlog ()
	{
		List<String> queries = new ArrayList<String>(); 

		try {
			PreparedStatement pStatement = connection.prepareStatement(DB_LOG_QUERY);
			ResultSet result = pStatement.executeQuery();
			
			while (result.next()) {
				queries.add(result.getString(1));
			}
			
			System.err.println("DB log was successfully loaded");			
		} catch (SQLException e) {
			System.err.println("Error occured while feeling DB structure");
			e.printStackTrace();
		}
		
		return queries;
	}
	
	public void storeResults(List<String> resultQueries)
	{
		try {
			PreparedStatement pStatement = connection.prepareStatement(DB_STORING_QUERY);
			int counter  = 0;
	        for (String query : resultQueries) {
	        	pStatement.setString(1, query);
	        	pStatement.addBatch();
	            if ((counter + 1) % 100 == 0) {
	            	pStatement.executeBatch();
	            	System.out.println(counter + 1 + " rows were written to DB");
	            }
	            counter++;
	        }
	        pStatement.executeBatch();
			System.err.println("Resolved queries were successfully writen to DB");	
		} catch (SQLException e) {
			System.err.println("Error occured while storing queryies' collection");
			e.printStackTrace();
		}
	}
	
	public void init()
	{
		try {
			boolean alreadyExists = new File(outputFile).exists();
			csvOutput = new CsvWriter(new FileWriter(outputFile, true), ',');
			if (!alreadyExists)
			{
				csvOutput.write("query_id");
				csvOutput.write("table");
				csvOutput.write("column");
				csvOutput.endRecord();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public void stroreParsedTables(Map<String, List<String>> result)
	{
		try {			
			for(Map.Entry<String, List<String>> entry : result.entrySet())
			{
				for(String column : entry.getValue())
				{
					csvOutput.write(new Integer(counter).toString());
					csvOutput.write(entry.getKey());
					csvOutput.write(column);
					csvOutput.endRecord();
				}				
			}	
			
			counter++;
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public void flush()
	{
		csvOutput.close();
	}
}
